package com.lqm.hibernate;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

import com.lqm.hibernate.model.EmployeeDTO;
import com.lqm.hibernate.model.Employee;
import com.lqm.hibernate.model.News;
import com.lqm.hibernate.model.HibernateUtil;

public class TestDemo1 {
	@Test
	public void test1(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 基于参数位置查询
			 * 位置从0开始
			 */
			Query query = session.createQuery("from News where author=? and id=? ");
			query.setParameter(1, 10);
			query.setParameter(0, "admin");
			
			
			
			List<News> list = query.list();
			System.out.println(list);
			
		} catch (Exception e) {
			e.printStackTrace();
	}
}
	@Test
	public void test02(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 命名参数查询
			 * 格式:属性=:参数名称
			 */
			Query query = session.createQuery("from News where author=:author and id=:id ");
			query.setParameter("author", "admin");
			query.setParameter("id", 10);
			
			List<News> list = query.list();
			System.out.println(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test03(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 命名参数查询
			 * 格式:属性=:参数名称
			 */
			List<News> list = session.createQuery("from News where id in (?,?,?)")
			.setParameter(0, 5)
			.setParameter(1, 10)
			.setParameter(2, 15)
			.list();
			System.out.println(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test04(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * IN查询
			 * setParameterList
			 */
			Integer[] ids = new Integer[]{6,10,23};
			List<News> list = session.createQuery("from News where id in (:ids)")
			.setParameterList("ids", ids)
			.list();
			System.out.println(list);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test05(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * like查询
			 * 
			 */
			List<News> list = session.createQuery("from News where title like ?")
			.setParameter(0, "%hello%")
			.list();
			System.out.println(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test06(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * like查询
			 */
			List<News> list = session.createQuery("from News where title like :like")
			.setParameter("like", "%hello%")
			.list();
			System.out.println(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test07(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 单对象查询
			 * .uniqueResult();
			 */
			News news = (News)session.createQuery("from News where id = :id")
					.setParameter("id", 10)
					.uniqueResult();
			System.out.println(news);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test08(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 聚合函数(count)查询
			 * 返回Long类型
			 */
			Long count = (Long)session.createQuery("select count(id) from News")
			.uniqueResult();
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test09(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 投影查询
			 */
			List<Object[]> list = session.createQuery("select id,title,author,pic from News")
								.list();
			//遍历
			for(Object[] objs : list){
				System.out.println(objs[0]+"--"+objs[1]+"--"+objs[2]+"--"+objs[3]);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test10(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 基于DTO（数据传输对象）查询
			 * new News(id,title,author,pic)
			 */
			List<News> list = session.createQuery("select new News(id,title,author,pic) from News")
			.list();
			System.out.println(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test11(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 分页查询
			 */
			List<News> list = session.createQuery("from News")
			.setFirstResult(5)  //limit 参数1
			.setMaxResults(5)   //limit 参数2  pageSize
			.list();
			for(News news : list){
				System.out.println(news.getId()+"--"+news.getTitle());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test12(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 导航查询
			 */
			List<Employee> list = session.createQuery("from Employee where dept.id = ?")
			.setParameter(0, 1)
			.list();
			for(Employee emp : list){
				System.out.println(emp.getId()+"--"+emp.getName()+"--"+emp.getDept().getName());
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test13(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 多表联合查询
			 * 基于字段查询取别名一定要加上关键字as
			 */
			List<Object[]> list = session.createQuery("select e.id as eid,e.name as ename,d.name as dname from Employee e inner join Department d on e.dept.id = d.id")
			.list();
			for(Object[] objs : list){
				System.out.println(objs[0]+"---"+objs[1]+"---"+objs[2]);
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
	
	@Test
	public void test14(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			/*
			 * 多表联合查询
			 * 基于字段查询取别名一定要加上关键字as
			 */
			List<EmployeeDTO> list = session.createQuery("select new com.wzm.hibernate.dto.EmployeeDTO(e.id as eid,e.name as ename,d.name as dname) from Employee e inner join Department d on e.dept.id = d.id")
			.list();
			for(EmployeeDTO emp : list){
				System.out.println(emp.getEid()+"--"+emp.getEname()+"--"+emp.getDname());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			HibernateUtil.close(session);
		}
	}
}

